﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common; 
using System.Data;
using System.Globalization;

using DbSharp.Framework.DbObject;
using DbSharp.Utils.Db;
using DbSharp.Framework.Common;
using DbSharp.Framework.Driver.FieldPersistence;
using System.Data.SqlClient;


namespace DbSharp.Framework.Driver
{
    /// <summary>
    /// 1. CREATE PROCEDURE (Transact-SQL) http://msdn.microsoft.com/en-us/library/ms187926.aspx
    /// 2. Table-Valued Parameters (Database Engine) http://msdn.microsoft.com/en-us/library/bb510489.aspx
	/// 3. Table-Valued Parameters in SQL Server 2008 (ADO.NET) http://msdn.microsoft.com/en-us/library/bb675163.aspx
    /// 4. Retrieving Scalar Data from a SQL Server Stored Procedure http://aspnet.4guysfromrolla.com/articles/062905-1.aspx
    /// 5. DateTimeOffset type http://www.simple-talk.com/community/blogs/andras/archive/2008/06/20/60191.aspx
    /// 6. table-valued parameter for Developer http://www.simple-talk.com/community/blogs/andras/archive/2008/08/27/69298.aspx
    /// </summary>
    public class SqlServerTestPlugin : ITestDataSource
    {

        private const int RECORDSET_ARGUMENT_FLAG = -1;

        private int GetArgumentType(string argumentTypeText)
        {
            int parameterType;
            string upperTypeStr = argumentTypeText.Trim().ToUpper();
            switch (upperTypeStr)
            {
                //1. About output cursor type parameter
                //In stored procedure, you can define a cursor type parameter, it must be output direction. 
                //But In ADO.net server side cursor does not supported

                //2. Table-Valued Parameter
                //System.Data.SqlClient supports populating table-valued parameters from DataTable, DbDataReader or IEnumerable objects. 
                //You must specify a type name for the table-valued parameter by using the TypeName property of a SqlParameter. 
                //The TypeName must match the name of a compatible type previously created on the server. 				
                case "MSSQL_BIGINT":
                    parameterType = (int)SqlDbType.BigInt;
                    break;
                case "MSSQL_BIT":
                    parameterType = (int)SqlDbType.Bit;
                    break;
                case "MSSQL_CHAR":
                    parameterType = (int)SqlDbType.Char;
                    break;
                case "MSSQL_DATETIME":
                    parameterType = (int)SqlDbType.DateTime;
                    break;
                case "MSSQL_DECIMAL":
                case "MSSQL_NUMERIC":
                    parameterType = (int)SqlDbType.Decimal;
                    break;
                case "MSSQL_FLOAT":
                    parameterType = (int)SqlDbType.Float;
                    break;
                case "MSSQL_INT":
                    parameterType = (int)SqlDbType.Int;
                    break;
                case "MSSQL_MONEY":
                    parameterType = (int)SqlDbType.Money;
                    break;
                case "MSSQL_NCHAR":
                    parameterType = (int)SqlDbType.NChar;
                    break;
                case "MSSQL_NTEXT":
                    parameterType = (int)SqlDbType.NText;
                    break;
                case "MSSQL_NVARCHAR":
                    parameterType = (int)SqlDbType.NVarChar;
                    break;
                case "MSSQL_REAL":
                    parameterType = (int)SqlDbType.Real;
                    break;
                case "MSSQL_GUID":
                    parameterType = (int)SqlDbType.UniqueIdentifier;
                    break;
                case "MSSQL_SMALLDATETIME":
                    parameterType = (int)SqlDbType.SmallDateTime;
                    break;
                case "MSSQL_SMALLINT":
                    parameterType = (int)SqlDbType.SmallInt;
                    break;
                case "MSSQL_SMALLMONEY":
                    parameterType = (int)SqlDbType.SmallMoney;
                    break;
                case "MSSQL_TEXT":
                    parameterType = (int)SqlDbType.Text;
                    break;
                case "MSSQL_TIMESTAMP":
                    parameterType = (int)SqlDbType.Timestamp;
                    break;
                case "MSSQL_TINYINT":
                    parameterType = (int)SqlDbType.TinyInt;
                    break;
                case "MSSQL_VARCHAR":
                    parameterType = (int)SqlDbType.VarChar;
                    break;
                case "MSSQL_RECORDSET":
                    parameterType = RECORDSET_ARGUMENT_FLAG;
                    break;
                //case "MSSQL_INPUTTABLE":
                //    parameterType = (int)SqlDbType.Structured;
                //    break; 
                case "MSSQL_DATE":
                    parameterType = (int)SqlDbType.Date;
                    break;
                case "MSSQL_TIME":
                    parameterType = (int)SqlDbType.Time;
                    break;
                case "MSSQL_DATETIME2":
                    parameterType = (int)SqlDbType.DateTime2;
                    break;
                case "MSSQL_DATETIMEOFFSET":
                    parameterType = (int)SqlDbType.DateTimeOffset;
                    break;
                default:
                    parameterType = (int)SqlDbType.VarChar;
                    break;
            }
            return parameterType;
        }

        private string EndWithSemiColons(string sql)
        {
            return SqlTextHelper.EndWithCharacter(sql, ';');
        }



        #region ITestDataSource Members

        /// <summary>
        /// SQL Server is not like Oracle, the latter hold the resultset with out-type argument, 
        /// but the former does not hold resultset with argument, you must retrieve resultset according to resultset occurred position.
        /// </summary>
        public bool HoldResultsetMustWithNamedArgInSP
        {
            get { return false; }
        }



        public DateTime GetDbServerTime(DbConnection conn)
        {
            string sql = "Select getdate() ; ";
            DbCommand cmd = new SqlCommand();
            cmd.Connection = conn;
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = sql;
            DateTime result = (DateTime)cmd.ExecuteScalar();
            return result;
        }

        public bool IsRecordsetTypeArgument(string argumentTypeText)
        {
            if (GetArgumentType(argumentTypeText) == RECORDSET_ARGUMENT_FLAG)
                return true;
            else
                return false;
        }

        public bool IsRecordsetTypeParameter(DbParameter param)
        {
            return false;
        }

        public DbConnection CreateConnection(string connString)
        {
            SqlConnection connection = new SqlConnection();
            connection.ConnectionString = connString;
            return connection;
        }


        public string NormalizeArgumentName(string argumentName)
        {
            return SqlTextHelper.BeginWithCharacter(argumentName, '@');
        }

        public DbCommand PrepareCommand(IStatementDeclaration statement)
        {
            DbCommand cmd = new SqlCommand();
            cmd.CommandType = statement.CommandType;

            IList<StatementArgument> listArgumentObj = statement.ListArgument;
            foreach (var argumentObj in listArgumentObj)
            {
                if (argumentObj.Direction == ArgumentDirection.ClientSide)
                {
                    //Clientside parameter will not added into ParameterList for ever
                    continue;
                }
                else if (argumentObj.IsSqlStmtResultArg)
                {
                    //It means this argument is virtual argument, not the real argument
                    // So the argument will not be added into ParameterList 
                    continue;
                }

                if (argumentObj.Direction == ArgumentDirection.AnonymousOutput)
                {
                    //Sql server will use anonymous arguments to hold resultsets, not need add these arguments into parameterlist
                    continue;
                }

                SqlParameter parameter = new SqlParameter();
                parameter.Direction = ArgumentDirectionHelper.ConvertToParamDirection(argumentObj.Direction);
                parameter.SqlDbType = (SqlDbType)GetArgumentType(argumentObj.TypeStr);
                parameter.Size = argumentObj.Size;
                parameter.ParameterName = NormalizeArgumentName(argumentObj.Name);

                cmd.Parameters.Add(parameter);
            }

            return cmd;
        }


        public void UpdateCommandText(DbCommand cmd, string newCmdText)
        {
            cmd.CommandText = newCmdText.Trim();
            //make sure the sql statement does end with semicolon
            if (cmd.CommandType == CommandType.Text)
            {
                cmd.CommandText = EndWithSemiColons(cmd.CommandText);
            }
        }

        public void AssignParameterValue(DbParameter param, string value, string dateTimeFormat)
        {
            //init parameters' value
            SqlParameter parameter = (SqlParameter)param;
            if ((parameter.SqlDbType == SqlDbType.DateTime)
                || (parameter.SqlDbType == SqlDbType.SmallDateTime)
                || (parameter.SqlDbType == SqlDbType.Date)
                || (parameter.SqlDbType == SqlDbType.Time)
                || (parameter.SqlDbType == SqlDbType.DateTime2)
                )
            {
                if (string.IsNullOrEmpty(dateTimeFormat))
                    parameter.Value = DateTimeParser.ParseUtc(value);
                else
                    parameter.Value = DateTimeParser.ParseExactUtc(value, dateTimeFormat);
            }
            else if (parameter.SqlDbType == SqlDbType.DateTimeOffset)
            {
                if (string.IsNullOrEmpty(dateTimeFormat))
                    parameter.Value = DateTimeParser.ParseUtc(value);
                else
                    parameter.Value = DateTimeParser.ParseExactUtcOffset(value, dateTimeFormat);
            }
            else if (parameter.SqlDbType == SqlDbType.Timestamp)
            {
                byte[] valueBytes = null;
                try
                {
                    valueBytes = Convert.FromBase64String(value);
                }
                catch
                {
                    string errorFmt = "The value of Timestamp data type must encoded in Base64 string format.";
                    throw new DbSharpException(string.Format(errorFmt));
                }
                parameter.Value = valueBytes;
            }
            else if (parameter.SqlDbType == SqlDbType.Structured)
            {
                //TODO: implement Table valued parameter in sp
                throw new NotImplementedException("MSSQL_InputTable parameter is not implemented yet.");
            }
            else
                parameter.Value = value;

        }


        public bool TransactionSupported
        {
            get { return true; }
        }




        private IFieldPersistence m_FieldPersistenceService=new SqlServerFieldPersistence();
        public IFieldPersistence FieldPersistenceService
        {
            get { return m_FieldPersistenceService; }
        }

        #endregion
    }
}
